﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Threading.Tasks;
using System.Configuration;

namespace StuManDAL.Common
{
    /// <summary>
    /// SqlServer访问类
    /// </summary>
    public class SQLHelper
    {
        private static SQLHelper _instance;
        public static SQLHelper Instance
        {
            get
            {
                if (_instance==null)
                {
                    _instance = new SQLHelper();
                }
                return _instance;
            }
        }
        private SQLHelper()
        {

        }
        private readonly string _connstring = ConfigurationManager.ConnectionStrings["ConnString"].ToString();
        /// <summary>
        /// 执行增、删、改方法
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <returns>受影响的数据条数</returns>
        public int Update(string sql)
        {
            SqlConnection connection = new SqlConnection(_connstring);
            SqlCommand command = new SqlCommand(sql, connection);
            try
            {
                connection.Open();
                return command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {

                throw ex;
            }
            finally
            {
                connection.Close();
            }
        }
        /// <summary>
        /// 执行单一结果集查询
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <returns>查询结果</returns>
        public object GetSingleResult(string sql)
        {
            SqlConnection conn = new SqlConnection(_connstring);
            SqlCommand command = new SqlCommand(sql, conn);
            try
            {
                conn.Open();
                return command.ExecuteScalar();
            }
            catch (Exception ex)
            {

                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }
        /// <summary>
        /// 执行结果集查询
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <returns>查询结果集</returns>
        public SqlDataReader GetReader(string sql)
        {
            SqlConnection conn = new SqlConnection(_connstring);
            SqlCommand command = new SqlCommand(sql, conn);
            try
            {
                conn.Open();
                return command.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }
        /// <summary>
        /// 执行查询返回DataSet
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <returns>填充查询结果的DataSet</returns>
        public DataSet GetDataSet(string sql)
        {
            SqlConnection conn = new SqlConnection(_connstring);
            SqlCommand command = new SqlCommand(sql, conn);
            SqlDataAdapter adapter = new SqlDataAdapter(command);
            DataSet dataSet = new DataSet();
            try
            {
                conn.Open();
                adapter.Fill(dataSet);
                return dataSet;
            }
            catch (Exception ex)
            {

                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }
    }
}
